using System;
using System.Collections.Generic;
using System.Text;
using emp.IDAL.SysManage;
using emp.Model.SysManage;
using System.Collections;
using System.Data.SqlClient;
using System.Data;
using emp.Utility;

namespace emp.SqlServerDAL.SysManage
{
    public class RoleUser : IRoleUser
    {
        #region IRoleUser Members


        private const string SQL_SELECT_BY_USER = "  select role_user_uid,role_uid,user_uid from system_role_user where user_uid = @User_ID";
        private const string SQL_SELECT_BY_ROLE = " select role_user_uid,role_uid,user_uid from system_role_user where role_uid = @Role_ID";

        private const string PARM_USERID = "@User_ID";
        private const string PARM_ROLEID = "@Role_ID";


        public int InsertByUser(RoleUserInfo myRoleUser)
        {
            string sRoleIDStr = myRoleUser.RoleIDStr.Trim();
            if (string.IsNullOrEmpty(sRoleIDStr))
            {
                return 0;
            }

            string[] roleArray = sRoleIDStr.Split(',');

            string sSqlInsert = " delete from system_role_user where user_uid = " + myRoleUser.UserID.ToString().Trim() + ";";

            foreach (string sRoleID in roleArray)
            {
                sSqlInsert += " insert into system_role_user values(" + sRoleID + "," + myRoleUser.UserID.ToString().Trim() + ");";
            }


            int returnValue = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sSqlInsert, null);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.RoleUser.InsertByUser(RoleUserInfo myRoleUser)";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return returnValue;
        }

        public int InsertByRole(RoleUserInfo myRoleUser)
        {
            string sUserIDStr = myRoleUser.UserIDStr.Trim();
            if (string.IsNullOrEmpty(sUserIDStr))
            {
                return 0;
            }

            string[] userArray = sUserIDStr.Split(',');

            string sSqlInsert = " delete from system_role_user where role_uid = " + myRoleUser.RoleID.ToString().Trim() + ";";

            foreach (string sUserID in userArray)
            {
                sSqlInsert += " insert into system_role_user values(" + myRoleUser.RoleID.ToString().Trim() + "," + sUserID + ");";
            }


            int returnValue = 0;
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        returnValue = SQLHelper.ExecuteNonQuery(trans, CommandType.Text, sSqlInsert, null);
                        trans.Commit();
                    }
                    catch (SqlException err)
                    {
                        trans.Rollback();
                        string sMethod = "emp.SqlServerDAL.RoleUser.InsertByRole(RoleUserInfo myRoleUser)";
                        EventsLog.WriteLog(err, sMethod);
                    }
                }
            }
            return returnValue;
        }

        public IList getListByUser(int iUserID)
        {
            IList roleList = new ArrayList();

            SqlParameter parm = new SqlParameter(PARM_USERID, SqlDbType.Int, 4, "user_uid");
            parm.Value = iUserID;

            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_BY_USER, parm))
            {
                while (rdr.Read())
                {
                    RoleUserInfo myRoleUserInfo = new RoleUserInfo();
                    myRoleUserInfo.RoleUserID = rdr.GetInt32(0);
                    myRoleUserInfo.RoleID = rdr.GetInt32(1);
                    myRoleUserInfo.UserID = rdr.GetInt32(2);

                    roleList.Add(myRoleUserInfo);
                }
            }

            return roleList;
        }

        public IList getListByRole(int iRoleID)
        {
            IList userList = new ArrayList();

            SqlParameter parm = new SqlParameter(PARM_ROLEID, SqlDbType.Int, 4, "role_uid");
            parm.Value = iRoleID;

            //Execute a query to read the products
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_SELECT_BY_ROLE, parm))
            {
                while (rdr.Read())
                {
                    RoleUserInfo myRoleUserInfo = new RoleUserInfo();
                    myRoleUserInfo.RoleUserID = rdr.GetInt32(0);
                    myRoleUserInfo.RoleID = rdr.GetInt32(1);
                    myRoleUserInfo.UserID = rdr.GetInt32(2);

                    userList.Add(myRoleUserInfo);
                }
            }

            return userList;
        }

        #endregion
    }
}
